
import java.util.Properties;
import java.util.List;
import java.util.ArrayList;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.Timestamp;


public class DBUtil
{
    static Connection conn_=null;

    static Connection getVerticaConnection()  throws Exception
    {
        Connection conn=null;
        String jdbcDriver = "com.vertica.jdbc.Driver";
        Class.forName(jdbcDriver).newInstance();

        String jdbcUrl = "jdbc:vertica://c0043156.itcs.hp.com:5433/SQ";

        conn = DriverManager.getConnection(jdbcUrl, "vertica", "vertica");

        conn.setAutoCommit(true);
        return conn;
    }


    static List <String [] > getAllSocialIds() 
    {
        List< String[] > socialIds = new ArrayList< String[] >();

        try {
            if (null == conn_) {
                conn_ = getVerticaConnection();
            }

            String sqlstr = "select account_id, social_id"
                          + " from sq.user_config";

            Statement stmt = conn_.createStatement();
            ResultSet rs = stmt.executeQuery(sqlstr);
            while (rs.next()) {
                socialIds.add( new String[]{rs.getString(1),
                                            rs.getString(2)} );
            }
        }
        catch (SQLException e) {
            System.out.print("SQL Exception: " + e);
        }
        catch (Exception e) {
            System.out.print("Exception: " + e);
        }

        return socialIds;
    }


    static List <String [] > getAllHotwords() 
    {
        List< String[] > socialIds = new ArrayList< String[] >();

        try {
            if (null == conn_) {
                conn_ = getVerticaConnection();
            }

            String sqlstr = "select account_id, hotword "
                          + " from sq.keywords";

            Statement stmt = conn_.createStatement();
            ResultSet rs = stmt.executeQuery(sqlstr);
            while (rs.next()) {
                socialIds.add( new String[]{rs.getString(1),
                                            rs.getString(2)} );
            }
        }
        catch (SQLException e) {
            System.out.print("SQL Exception: " + e);
        }
        catch (Exception e) {
            System.out.print("Exception: " + e);
        }

        return socialIds;
    }


    static boolean putMessage(String socialId, long tsMsg, String msgId,
                              String msgText, String msgSrc, 
                              long tsInsert, String accountId)
    {
        try {
            if (null == conn_) {
                conn_ = getVerticaConnection();
            }

            String sqlstr = "insert into sq.social_message"
                          + "(social_id, message_ts, message_id"
                          + ", message_text, message_source"
                          + ", message_insert_ts, account_id)"
                          + " values (?,?,?,?,?,?,?)";
            PreparedStatement prest = conn_.prepareStatement(sqlstr, 
                                          ResultSet.TYPE_SCROLL_SENSITIVE,
                                          ResultSet.CONCUR_READ_ONLY);  
            prest.setString(1, socialId);
            prest.setTimestamp(2, new Timestamp(tsMsg));
            prest.setString(3, msgId);
            prest.setString(4, msgText);
            prest.setString(5, msgSrc);
            prest.setTimestamp(6, new Timestamp(tsInsert));
            prest.setString(7, accountId);

            prest.addBatch();
            prest.executeBatch();

            return true;
        }
        catch (Exception e) {
            System.out.print("Exception: " + e);
            return false;
        }
    }


    static boolean putProfile(String socialId, long tsInsert, 
                    String userName, String companyName, String companyId, 
                    String title, String location, String picture,
                    int version)

    {
        try {
            if (null == conn_) {
                conn_ = getVerticaConnection();
            }

            String sqlstr = "insert into sq.social_message"
                          + "(user_id, insert_ts, user_name"
                          + ", company_name, company_id"
                          + ", title, location, picture, version)"
                          + " values (?,?,?,?,?,?,?,?,?)";
            PreparedStatement prest = conn_.prepareStatement(sqlstr, 
                                          ResultSet.TYPE_SCROLL_SENSITIVE,
                                          ResultSet.CONCUR_READ_ONLY);  
            prest.setString(1, socialId);
            prest.setTimestamp(2, new Timestamp(tsInsert));
            prest.setString(3, userName);
            prest.setString(4, companyName);
            prest.setString(5, companyId);
            prest.setString(6, title);
            prest.setString(7, location);
            prest.setString(8, picture);
            prest.setInt(9, version);

            prest.addBatch();
            prest.executeBatch();

            return true;
        }
        catch (Exception e) {
            System.out.print("Exception: " + e);
            return false;
        }
    }

 
    static public void main(String [] args) {
        List< String[] > words = getAllHotwords();        

        for(String[] word : words) {
            System.out.println("AccountId: " + word[0] 
                           + ", hotword: " + word[1]);
        }
    }
}

